package com.lmx.persistence.service;

import java.text.ParseException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Scope;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.lmx.common.constants.CommonConstants;
import com.lmx.common.constants.ICommonConstants;
import com.lmx.common.context.ActionContextHolder;
import com.lmx.common.util.CommonUtil;
import com.lmx.repoDomain.tmodel.Bed;
import com.lmx.repoDomain.tmodel.Customer;
import com.lmx.repoDomain.tmodel.CustomerBusinessFlow;
import com.lmx.repoDomain.tmodel.CustomerServerChange;
import com.lmx.repoDomain.tmodel.Device;
import com.lmx.repoDomain.tmodel.Dicttype;
import com.lmx.repoDomain.tmodel.HbeaconWarning;
import com.lmx.repoDomain.tmodel.HealthInfo;
import com.lmx.repoDomain.tmodel.Message;
import com.lmx.repoDomain.tmodel.NursingLevel;
import com.lmx.repoDomain.tmodel.NursingProject;
import com.lmx.repoDomain.tmodel.PersonLocation;
import com.lmx.repoDomain.tmodel.StatQuery;
import com.lmx.repoDomain.tmodel.Tenant;
import com.lmx.repoDomain.tmodel.business.User;
import com.lmx.thirdPart.ThirdPartBusiException;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;


/**
 * @author  huibati
 * 2017年6月22日
 * @param <T>
 */
@Service
@Scope("prototype")
public class TestService<T> {

	private Log logger = LogFactory.getLog(this.getClass());

	@PersistenceContext
	private EntityManager em;

	@Autowired
	protected GenericService dslJpaService;
	
	@Autowired
	private CustomerService customerService;

	@Autowired
	protected DeviceService deviceService;
	
	@Autowired
	private HealthInfoService healthInfoService;
	
	@Autowired
	private EmployeeService employeeService;
	
	@Autowired
	private CommonService commonService;
	
	@Autowired
	private UserService userService;
	
	@Autowired
	protected WorkPlaceService workPlaceService;
	
	@Autowired
	protected AccountService accountService;
	
	@Resource(name = "jdbcTemplate")
	private JdbcTemplate jdbcTemplate;

	protected JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	// 操作退住审核通过
	@Transactional
	public void testCustomerBack() {
		try {
			Integer curTimeStart = CommonUtil
					.strToIntegerTime(CommonUtil.getCurrTimeForString().substring(0, 10) + " 00:00:00");
			Integer curTimeEnd = CommonUtil
					.strToIntegerTime(CommonUtil.getCurrTimeForString().substring(0, 10) + " 23:59:59");
			List<CustomerBusinessFlow> flowList = this.em
					.createQuery(
							"select a from CustomerBusinessFlow a where a.delStatus != ?1 and a.leaveStatus.typecode=?2 and a.createDateTime>=?3 and a.createDateTime<=?4 ")
					.setParameter(1, (short) 1).setParameter(2, CommonConstants.DICTTYPE.FLOW_LEAVE_STATUS_4.label()).setParameter(3, curTimeStart)
					.setParameter(4, curTimeEnd).getResultList();
			for (CustomerBusinessFlow flow : flowList) {
				customerService.operateCustomerBack(flow,flow.getCustomer(),0,null);
			}
		} catch (ParseException e) {
			logger.error("test error " + e);
		}
	}
	
	@Transactional
	public void testYesCustomerBack() {
		try {
			String dayTime = CommonUtil.getYesterdayCurrTimeForString();// 前天日期
			Integer yesTimeStart = CommonUtil.strToIntegerTime(dayTime
					.substring(0, 10) + " 00:00:00");
			Integer yesTimeEnd = CommonUtil.strToIntegerTime(dayTime.substring(
					0, 10) + " 23:59:59");
			List<CustomerBusinessFlow> flowList = this.em
					.createQuery(
							"select a from CustomerBusinessFlow a where a.delStatus != ?1 and a.leaveStatus.typecode=?2 and a.createDateTime>=?3 and a.createDateTime<=?4 ")
					.setParameter(1, (short) 1).setParameter(2, CommonConstants.DICTTYPE.FLOW_LEAVE_STATUS_4.label()).setParameter(3, yesTimeStart)
					.setParameter(4, yesTimeEnd).getResultList();
			for (CustomerBusinessFlow flow : flowList) {
				customerService.operateCustomerBack(flow,flow.getCustomer(),1,null);
			}
		} catch (ParseException e) {
			logger.error("test error " + e);
		}
	}
	
	// 模拟推送数据
	public void startShowDeviceData(Long tenantId) {
		try {
			ActionContextHolder.setShowFlag("on");
//			while(true){
				String showFlag = ActionContextHolder.getShowFlag();
				if ("on".equals(showFlag)) {
//					Thread.sleep(CommonUtil.randomInt(20,10) * 1000);
					createDeviceData(tenantId);
					System.out.println("on "+CommonUtil.getCurrTimeForString());
				}else{
					System.out.println("over "+CommonUtil.getCurrTime());
//					break;
				}
//			}
		} catch (Exception e) {
			logger.error("test error " + e);
		}
	}
	
	// 模拟推送数据
	public void endShowDeviceData() {
		try {
			ActionContextHolder.setShowFlag("off");
		} catch (Exception e) {
			logger.error("test error " + e);
		}
	}
	
	
	/**
	 * @author huibati
	 * @param 产生模拟设备数据
	 *            2017年6月22日
	 */
	@Transactional
	public void createDeviceData(Long tenantId) {
		// scanner列表
		List<Device> scanners = new ArrayList<>();
		StringBuilder queryScanner = new StringBuilder(
				"select c from Device c where c.delStatus !=1 and c.tenant.id = ?1 and c.deviceType.typecode=?2 and c.bindStatus.typecode=?3 ");
		scanners = this.em.createQuery(queryScanner.toString()).setParameter(1, tenantId).setParameter(2, "deviceType2")
				.setParameter(3, "bindStatus1").getResultList();
		// band列表
		List<Device> bands = new ArrayList<>();
		StringBuilder queryBand = new StringBuilder(
				"select c from Device c where c.delStatus !=1 and c.tenant.id = ?1 and c.deviceType.typecode=?2 and c.bindStatus.typecode=?3 and c.remark!=?4 ");
		bands = this.em.createQuery(queryBand.toString()).setParameter(1, tenantId).setParameter(2, "deviceType4")
				.setParameter(3, "bindStatus1").setParameter(4, "real").getResultList();
		// button列表
		List<Device> buttons = new ArrayList<>();
		StringBuilder queryButton = new StringBuilder(
				"select c from Device c where c.delStatus !=1 and c.tenant.id = ?1 and c.deviceType.typecode=?2 and c.bindStatus.typecode=?3 and c.remark!=?4 ");
		buttons = this.em.createQuery(queryButton.toString()).setParameter(1, tenantId).setParameter(2, "deviceType3")
				.setParameter(3, "bindStatus1").setParameter(4, "real").getResultList();
		// 益体康卡列表
		List<Device> cards = new ArrayList<>();
		StringBuilder queryCard = new StringBuilder(
				"select c from Device c where c.delStatus !=1 and c.tenant.id = ?1 and c.deviceType.typecode=?2 and c.bindStatus.typecode=?3 and c.remark!=?4 ");
		cards = this.em.createQuery(queryCard.toString()).setParameter(1, tenantId).setParameter(2, "deviceType6")
				.setParameter(3, "bindStatus1").setParameter(4, "real").getResultList();
		// 床垫列表
		List<Device> bedMats = new ArrayList<>();
		StringBuilder queryBedMat = new StringBuilder(
				"select c from Device c where c.delStatus !=1 and c.tenant.id = ?1 and c.deviceType.typecode=?2 and c.bindStatus.typecode=?3 and c.remark!=?4 ");
		bedMats = this.em.createQuery(queryBedMat.toString()).setParameter(1, tenantId).setParameter(2, "deviceType5")
				.setParameter(3, "bindStatus1").setParameter(4, "real").getResultList();
		// 位置数据
		String result = "{\"locationData\":[";
		List<Integer> list1 = new ArrayList<>();
		for (int i = 0; i < bands.size() && i < 10; i++) {
			int bandIndex = CommonUtil.randomInt(bands.size(), 0);
			while (true) {
				if (list1.contains(bandIndex)) {
					bandIndex = CommonUtil.randomInt(bands.size(), 0);
				} else {
					list1.add(bandIndex);
					break;
				}
			}
			if (i == 0) {
				result += "{\"dataTime\":" + CommonUtil.getCurrTimes() 
				        + ",\"scanner\":\""+ scanners.get(CommonUtil.randomInt(scanners.size(), 0)).getSeq() + "\""
				        + ",\"deviceId\":\""
						+ bands.get(bandIndex).getSeq() + "\"}";
			} else {
				result += ",{\"dataTime\":" + CommonUtil.getCurrTimes() 
				        + ",\"scanner\":\"" + scanners.get(CommonUtil.randomInt(scanners.size(), 0)).getSeq() + "\""
				        + ",\"deviceId\":\""
						+ bands.get(bandIndex).getSeq() + "\"}";
			}
		}
		result += "]}";
		if (bands != null && bands.size() > 0) {
			deviceService.updateDeviceLocationData(result);// 更新位置信息
		}
		// 益体康健康数据
		String result2 = "[";
		List<Integer> list2 = new ArrayList<>();
		for (int i = 0; i < cards.size() && i < 10 ; i++) {
			int cardIndex = CommonUtil.randomInt(cards.size(), 0);
			while (true) {
				if (list2.contains(cardIndex)) {
					cardIndex = CommonUtil.randomInt(cards.size(), 0);
				} else {
					list2.add(cardIndex);
					break;
				}
			}
			if (i == 0) {
				// 血压
				result2 += "{ \"apptype\" : \"BloodPressureV1\", \"datakey\" : \""
						+ "0200000000130005250301011429000028000000000000" + cards.get(cardIndex).getSeq() + "\","
						+ "\"adddate\" : \"" + CommonUtil.getCurrTimeForString()
						+ "\", \"EntAccount\" :\"testEnterprise\", \"DataId\" : \"" 
						+ CommonUtil.getValidationNum()
						+ "\", " + "\"collectdate\": \"" + CommonUtil.getCurrTimeForString()
						+ "\", \"diastolicpressure\" :"
						+ CommonUtil.randomInt(90, 60)
						+ ", \"systolicpressure\" : "
						+ CommonUtil.randomInt(140, 90)
						+ ",\"pulse\" : 50.0}";
				// 血氧
				result2 += ",{ \"apptype\" : \"Spo2DataV1\", \"datakey\" : \""
						+ "0200000000130005250301011429000028000000000000" + cards.get(cardIndex).getSeq() + "\","
						+ "\"adddate\" : \"" + CommonUtil.getCurrTimeForString()
						+ "\", \"EntAccount\" :\"testEnterprise\", \"DataId\" : \"" + CommonUtil.getValidationNum()
						+ "\", " + "\"collectdate\": \"" + CommonUtil.getCurrTimeForString()
						+ "\", \"oxygen\" :"
						+ CommonUtil.randomInt(99, 96)
						+ ",\"pulse\" : 50.0}";
			} else {
				result2 += ",{ \"apptype\" : \"BloodPressureV1\", \"datakey\" : \""
						+ "0200000000130005250301011429000028000000000000" + cards.get(cardIndex).getSeq() + "\","
						+ "\"adddate\" : \"" + CommonUtil.getCurrTimeForString()
						+ "\", \"EntAccount\" :\"testEnterprise\", \"DataId\" : \"" + CommonUtil.getValidationNum()
						+ "\", " + "\"collectdate\": \"" + CommonUtil.getCurrTimeForString()
						+ "\", \"diastolicpressure\" : "
						+ CommonUtil.randomInt(90, 60)
						+ ", \"systolicpressure\" :"
						+ CommonUtil.randomInt(140, 90)
						+ ",\"pulse\" : 50.0}";
				// 血氧
				result2 += ",{ \"apptype\" : \"Spo2DataV1\", \"datakey\" : \""
						+ "0200000000130005250301011429000028000000000000" + cards.get(cardIndex).getSeq() + "\","
						+ "\"adddate\" : \"" + CommonUtil.getCurrTimeForString()
						+ "\", \"EntAccount\" :\"testEnterprise\", \"DataId\" : \"" + CommonUtil.getValidationNum()
						+ "\", " + "\"collectdate\": \"" + CommonUtil.getCurrTimeForString()
						+ "\", \"oxygen\" : "
						+ CommonUtil.randomInt(99, 96)
						+ ",\"pulse\" : 50.0}";
			}
		}
		result2 += "]";
		JSONArray jsonArray = JSONArray.fromObject(result2);
		for (int i = 0; i < jsonArray.size(); i++) {
			JSONObject object = jsonArray.getJSONObject(i);
			healthInfoService.addEtcHealthInfo(object);
		}

		//床垫数据
		String result3= "{\"retCode\": 0,\"message\": \"test\","+"\"data\": {" ;
		List<Integer> list3 = new ArrayList<>();
		for(int i = 0; i < bedMats.size() && i < 10 ; i++){
			int bedMatIndex = CommonUtil.randomInt(bedMats.size(), 0);
			while (true) {
				if (list3.contains(bedMatIndex)) {
					bedMatIndex = CommonUtil.randomInt(bedMats.size(), 0);
				} else {
					list3.add(bedMatIndex);
					break;
				}
			}
			if(i==0){
				result3+= "\""
						+ bedMats.get(bedMatIndex).getSeq()
						+ "\": {"
						+ "\"heart\":[{\"markTime\": "
						+ CommonUtil.getCurrTimes()
						+ ",\"val\": "
						+ CommonUtil.randomInt(100, 60)
						+ "}],\"status\": [{\"markTime\": "
						+ CommonUtil.getCurrTimes()
						+ ",\"val\": 6"
						+ "}]," 
						+ "\"motion\":[]," 
						+ "\"breath\":[{\"markTime\": "
						+ CommonUtil.getCurrTimes()
						+ ",\"val\": "
						+ CommonUtil.randomInt(20, 15)
						+ "}]}" ;
			}else{
				result3+= ",\""
						+ bedMats.get(bedMatIndex).getSeq()
						+ "\": {"
						+ "\"heart\":[{\"markTime\": "
						+ CommonUtil.getCurrTimes()
						+ ",\"val\": "
						+ CommonUtil.randomInt(100, 60)
						+ "}],\"status\": [{\"markTime\": "
						+ CommonUtil.getCurrTimes()
						+ "," + "\"val\": 6"
						+ "}]," 
						+ "\"motion\":[]," 
						+ "\"breath\":[{\"markTime\": "
						+ CommonUtil.getCurrTimes()
						+ ",\"val\": "
						+ CommonUtil.randomInt(20, 15)
						+ "}]}" ;
			}
		}
		result3+= "}}";
		if (bedMats != null && bedMats.size() > 0) {
			getDeviceStatus(result3);
		}
	}
	
	@Transactional
	public void getDeviceStatus(String res)  {
		List infoList = new ArrayList();
		List deviceList = new ArrayList();
		try {
			logger.debug("getDeviceStatus res:" + res);
			JSONObject resObj = JSONObject.fromObject(res);
			// 处理异常
			if (resObj.get("error_response") != null) {
				throw new ThirdPartBusiException(resObj.getJSONObject("error_response").getString("code"),
						resObj.getJSONObject("error_response").getString("sub_code"));
			}
			Integer retCode = resObj.getInt("retCode");
			JSONArray deviceStatus = new JSONArray();
			// code为0表示正常返回
			if (retCode.intValue() == 0) {
				String message = resObj.getString("message");
				JSONObject dataNode = resObj.getJSONObject("data");
				Iterator<String> it = dataNode.keys();
				while (it.hasNext()) {
					// key就是设备号
					String deviceNo = it.next();
					JSONObject item = dataNode.getJSONObject(deviceNo);
					JSONObject ds = new JSONObject();
					ds.put("deviceNo", deviceNo);
					// 取得设备状态，更新设备信息
					JSONArray statusData = item.getJSONArray("status");
					if (statusData.size() > 0) {
						Integer val = statusData.getJSONObject(0).getInt("val");
						// 设备信息获取
						ds.put("status", val);
						deviceStatus.add(ds);
					}
					// 信号数据
					// System.out.println(item.getString("motion"));
					if (item.containsKey("signal") && !"[]".equals(item.getString("signal"))) {
						JSONArray signalData = item.getJSONArray("signal");
						if (signalData.size() > 0) {
							Integer val = statusData.getJSONObject(0).getInt("val");
							// 设备信息获取
							ds.put("signal", val);
							deviceStatus.add(ds);
						}
					}
					Customer customer = this.deviceService.getCustomerWithDeviceNo(deviceNo);
					Device device = this.deviceService.getDeviceWithDeviceNo(deviceNo);
					if(device!=null){
						// 修改设备信息
						if (customer != null) {// 已绑定
							device.setStatus(commonService.getDicttype("deviceStatus2"));// 已占用
						} else {
							device.setStatus(commonService.getDicttype("CommonConstants.DICTTYPE.DEVICE_STATUS_1.label()"));
						}
						device.setStatusDate(CommonUtil.getCurrTimes());//设置设备状态时间
						device.setDeviceStatus((short)1);
						deviceList.add(device);
					}
					if (customer != null) {
						// 取得心率数据，更新相关信息
						if (item.containsKey("heart") && !"[]".equals(item.getString("heart"))) {
							JSONArray heartData = item.getJSONArray("heart");
							if (heartData.size() > 0) {
								// for(int i=0;i<heartData.size();i++){
								// 只取时间最近的那条
								for (int i = heartData.size() - 1; i < heartData.size()&&i>=0; i--) {
									Integer val = heartData.getJSONObject(i).getInt("val");
									if (val == -100 || val == 65436) {// 无效数据
										continue;
									} else {
										HealthInfo info = new HealthInfo();
										info.setExamUser(customer);
										info.setDelStatus((short) 0);
										info.setTenant(customer.getTenant());
										info.setDevice(device);// huibati
										// info.setExamDateTime(CommonUtil.getCurrTimes());
										info.setExamDateTime(heartData.getJSONObject(i).getInt("markTime"));
										info.setCreateDateTime((long)CommonUtil.getCurrTimes());
										// 处理健康类业务数据
										// TODO
										info.setResult(val.toString());
										// 组装并发送消息
										Dicttype rateType = commonService
												.getDicttype(CommonConstants.DICTTYPE.HEALTHEXAMTYPE_RATE.label());
										info.setItemtype(rateType);
										info.setResult(val.toString());
										info.setSourceFlag("fromDevice");
										Message msgObj = this.buildHealthMessage(customer, info);
										// 发送给队列管理器
										if (msgObj.getUserList() != null && msgObj.getUserList().size() > 0) {
											ActionContextHolder.getMessageQueue().addMessage(msgObj);// huibati
										}
										// healthInfoService.createHealthInfo(info);
										infoList.add(info);
										healthInfoService.createMessages(msgObj);
										break;
									}
								}
							}
						}
						// 体动数据
						if (item.containsKey("motion") && !"[]".equals(item.getString("motion"))) {
							JSONArray motionData = item.getJSONArray("motion");
						}
						// 呼吸数据
						if (item.containsKey("breath") && !"[]".equals(item.getString("breath"))) {
							JSONArray breathData = item.getJSONArray("breath");
							if (breathData.size() > 0) {
								//for(int i=0;i<breathData.size();i++){
								//只取时间最近的那条
								for (int i = breathData.size() - 1; i < breathData.size()&&i>=0; i--) {
									Integer val = breathData.getJSONObject(i).getInt("val");
									if (val == -100 || val == 65436) {// 无效数据
										continue;
									} else {
										HealthInfo info = new HealthInfo();
										info.setExamUser(customer);
										info.setDelStatus((short) 0);
										info.setTenant(customer.getTenant());
										info.setDevice(device);// huibati
										// info.setExamDateTime(CommonUtil.getCurrTimes());
										info.setExamDateTime(breathData.getJSONObject(i).getInt("markTime"));
										info.setCreateDateTime((long)CommonUtil.getCurrTimes());
										// 处理健康类业务数据
										// TODO
										info.setResult(val.toString());
										// 组装并发送消息
										Dicttype breathType = commonService
												.getDicttype(CommonConstants.DICTTYPE.HEALTHEXAMTYPE_BREATH.label());
										info.setItemtype(breathType);
										info.setResult(val.toString());
										info.setSourceFlag("fromDevice");
										Message msgObj = this.buildHealthMessage(customer, info);
										// 发送给队列管理器
										if (msgObj.getUserList() != null && msgObj.getUserList().size() > 0) {
											ActionContextHolder.getMessageQueue().addMessage(msgObj);// huibati
										}
										// healthInfoService.createHealthInfo(info);
										infoList.add(info);
										healthInfoService.createMessages(msgObj);
										break;
									}
								}
							}//呼吸数据
						}
					}
				}
				if(infoList.size()>0){
					commonService.batchInsert(infoList);//add 健康数据
				}
				if(deviceList.size()>0){
				    commonService.batchUpdate(deviceList);//更新设备时间
				}
			}
		} catch (Exception e) {
			logger.error("login io fail", e);
		} 
	}
	
	
    /*
     * 根据健康数据信息，生成提醒数据
     */
    @Transactional
    public Message buildHealthMessage(Customer customer,HealthInfo info){
        Message message = new Message();
        message.setDelStatus((short)0);
		message.setTenant(customer.getTenant());
        message.setType(new Integer(CommonConstants.MESSAGE.MESSAGETYPE_NOTICE.label()).shortValue());
        message.setCode(CommonConstants.MESSAGE.MESSAGECODE_HEALTHNOTICE.label());
        message.setCreateTime(CommonUtil.getCurrTimes());
        //使用健康数据类型作为通知的业务类型
        message.setBusiness(info.getItemtype().getTypecode());
        //拼装内容信息
        JSONObject content = new JSONObject();
        content.put("createTime", info.getCreateDateTime());
        content.put("customerId", customer.getId());
        content.put("customerName", customer.getName());
		content.put("result", info.getResult());
		content.put("resultAddtion", info.getResultAddtion());
		content.put("resultAddtionSec", info.getResultAddtionSec());
		content.put("resultAddtionThir",info.getResultAddtionThir());
		content.put("resultAddtionFou", info.getResultAddtionFou());
		content.put("resultAddtionFiv", info.getResultAddtionFiv());
		content.put("resultAddtionSix", info.getResultAddtionSix());
		content.put("resultAddtionSev", info.getResultAddtionSec());
		content.put("resultAddtionEig", info.getResultAddtionEig());
		content.put("resultAddtionNin", info.getResultAddtionNin());
		content.put("resultAddtionTen", info.getResultAddtionTen());
        message.setContent(content.toString());
        //其他信息
        message.setBusiness(info.getItemtype().getTypecode());
        message.setHasRead(new Integer(0).shortValue());
        //取得需要发送的用户--发送给所有本机构的人员
        message.setUserList(userService.getAllUserList(customer.getTenant()));
        return message;
    }
    
    
    
	// 删除数据
	@Transactional
	public void deleteStatData() {
		try {
			logger.debug("start delete stat Data ");
			String queryBed = "delete from StatQuery rp where rp.createDateTime> ?1";
			this.em.createQuery(queryBed).setParameter(1, 1496592000).executeUpdate();
		} catch (Exception e) {
			logger.debug("delete stat Data  error: " + e);
		}
	}
    
    public void statOldManQuery(String yesTime) {
		try {
			//String yesTime = CommonUtil.getYesterdayCurrTimeForString();// 前天日期
			Integer yesTimeStart = CommonUtil.strToIntegerTime(yesTime
					.substring(0, 10) + " 00:00:00");
			Integer yesTimeEnd = CommonUtil.strToIntegerTime(yesTime.substring(
					0, 10) + " 23:59:59");
			List<StatQuery> dataList = new ArrayList<>();
			// yesTimeStart=1494864000;//1494777600;//1494777600;
			// yesTimeEnd=1494950399;//1494863999;//1493740799;
			Iterator it;
			// 老人---性别统计 条件：删除状态不为1
			logger.info("stat17：" + CommonUtil.getCurrTimeForString());
			StringBuilder sql = new StringBuilder(
					"select t.sex_id as item ,count(*) as sum ,t.tenant_id  "
							+ "  from tnb_customer t where t.sex_id is not null and t.del_status != 1 "
							+ "  and t.create_time>=" + yesTimeStart
							+ "  and t.create_time<=" + yesTimeEnd
							+ "  group by t.sex_id,t.tenant_id  ");
			List dataOldSexCount = getJdbcTemplate().queryForList(
					sql.toString());
			it = dataOldSexCount.iterator();
			Dicttype type18 = commonService.getDicttype("statType18");
			while (it.hasNext()) {
				Map rece = (Map) it.next();
				StatQuery data = new StatQuery();
				data.setStatDate(yesTimeStart);
				data.setStatItemType(commonService.getDicttypeById((Long
						.parseLong(rece.get("item").toString()))));
				Tenant tenant = new Tenant();
				tenant.setId(Long.parseLong(rece.get("tenant_id").toString()));
				data.setTenant(tenant);
				data.setStatResult((Integer
						.parseInt(rece.get("sum").toString())));
				data.setCreateDateTime(CommonUtil.getCurrTimes());
				data.setStatType(type18);
				data.setDelStatus((short) 0);
				dataList.add(data);
			}
			// 老人---自理能力统计 条件：删除状态不为1
			logger.info("stat18：" + CommonUtil.getCurrTimeForString());
			sql.delete(0, sql.length());
			sql.append("select t.selfCareAbility_id as item ,count(*) as sum ,t.tenant_id "
					+ "  from tnb_customer t where t.selfCareAbility_id is not null and t.del_status != 1 "
					+ "  and t.create_time>="
					+ yesTimeStart
					+ "  and t.create_time<="
					+ yesTimeEnd
					+ "  group by t.selfCareAbility_id,t.tenant_id ");
			List dataOldSelfCount = getJdbcTemplate().queryForList(
					sql.toString());
			it = dataOldSelfCount.iterator();
			Dicttype type19 = commonService.getDicttype("statType19");
			while (it.hasNext()) {
				Map rece = (Map) it.next();
				StatQuery data = new StatQuery();
				data.setStatDate(yesTimeStart);
				data.setStatItemType(commonService.getDicttypeById((Long
						.parseLong(rece.get("item").toString()))));
				Tenant tenant = new Tenant();
				tenant.setId(Long.parseLong(rece.get("tenant_id").toString()));
				data.setTenant(tenant);
				data.setStatResult((Integer
						.parseInt(rece.get("sum").toString())));
				data.setCreateDateTime(CommonUtil.getCurrTimes());
				data.setStatType(type19);
				data.setDelStatus((short) 0);
				dataList.add(data);
			}
			// 老人---年龄统计 条件：删除状态不为1
			logger.info("stat19：" + CommonUtil.getCurrTimeForString());
			sql.delete(0, sql.length());
			sql.append("select elt(interval(t.age,0,70,80,90,100),'0-70','70-79','80-89', '90-99','> 100') as `ages`,count(*) as `sum` ,t.tenant_id "
					+ "  from tnb_customer t where t.age is not null and  t.del_status != 1  "
					+ "  and t.create_time>="
					+ yesTimeStart
					+ "  and t.create_time<="
					+ yesTimeEnd
					+ "  group by elt(interval(t.age,0,70,80,90,100),'0-70','70-79','80-89', '90-99','> 100'),t.tenant_id ");
			List dataOldAgeCount = getJdbcTemplate().queryForList(
					sql.toString());
			it = dataOldAgeCount.iterator();
			Dicttype type20 = commonService.getDicttype("statType20");
			Map<String, Dicttype> ageDicttype = new HashMap<>();
			ageDicttype.put("0-70", commonService.getDicttype("statItem4"));
			ageDicttype.put("70-79", commonService.getDicttype("statItem5"));
			ageDicttype.put("80-89", commonService.getDicttype("statItem6"));
			ageDicttype.put("90-99", commonService.getDicttype("statItem7"));
			ageDicttype.put("≥100", commonService.getDicttype("statItem8"));
			while (it.hasNext()) {
				Map rece = (Map) it.next();
				StatQuery data = new StatQuery();
				data.setStatDate(yesTimeStart);
				if ("0-70".equals(rece.get("ages").toString())) {
					data.setStatItemType(ageDicttype.get("0-70"));
				}
				if ("70-79".equals(rece.get("ages").toString())) {
					data.setStatItemType(ageDicttype.get("70-79"));
				}
				if ("80-89".equals(rece.get("ages").toString())) {
					data.setStatItemType(ageDicttype.get("80-89"));
				}
				if ("90-99".equals(rece.get("ages").toString())) {
					data.setStatItemType(ageDicttype.get("90-99"));
				}
				if ("> 100".equals(rece.get("ages").toString())) {
					data.setStatItemType(ageDicttype.get("≥100"));
				}
				Tenant tenant = new Tenant();
				tenant.setId(Long.parseLong(rece.get("tenant_id").toString()));
				data.setTenant(tenant);
				data.setStatResult(Integer.parseInt(rece.get("sum").toString()));
				data.setCreateDateTime(CommonUtil.getCurrTimes());
				data.setStatType(type20);
				data.setDelStatus((short) 0);
				dataList.add(data);
			}
			// 批存
			commonService.batchInsert(dataList);
			logger.debug("end");
		} catch (Exception e) {
			logger.debug("create OldMan stat error :" + e);
		}
	}

	/**
	 *
	 * （每日凌晨2点25执行咨询统计）
	 */
	public void statConsultQuery(String yesTime) {
		try {
			//String yesTime = CommonUtil.getYesterdayCurrTimeForString();// 前天日期
			Integer yesTimeStart = CommonUtil.strToIntegerTime(yesTime
					.substring(0, 10) + " 00:00:00");
			Integer yesTimeEnd = CommonUtil.strToIntegerTime(yesTime.substring(
					0, 10) + " 23:59:59");
			List<StatQuery> dataList = new ArrayList<>();
			// yesTimeStart=1494864000;//1494777600;//1494777600;
			// yesTimeEnd=1494950399;//1494863999;//1493740799;
			Iterator it;
			logger.info("stat9:" + CommonUtil.getCurrTimeForString());
			// 咨询---入住偏好 条件：咨询表 咨询时间
			StringBuilder sql = new StringBuilder(
					"select c.inPreference ,c.info.tenant, count(c.inPreference) from ConsultInpreference c where c.info in"
							+ " ( select a  from ConsultInfo a where a.delStatus != ?1 and  "
							+ "   a.advDate>=?2 and a.advDate<=?3  "
							+ " ) group by c.inPreference,c.info.tenant");
			List dataConsultInPreference = this.em.createQuery(sql.toString())
					.setParameter(1, (short) 1).setParameter(2, yesTimeStart)
					.setParameter(3, yesTimeEnd).getResultList();
			it = dataConsultInPreference.iterator();
			Dicttype type13 = commonService.getDicttype("statType13");
			while (it.hasNext()) {
				Object[] res = (Object[]) it.next();
				StatQuery data = new StatQuery();
				data.setDelStatus((short) 0);
				data.setStatDate(yesTimeStart);
				data.setStatItemType((Dicttype) res[0]);
				data.setTenant((Tenant) res[1]);
				data.setStatResult(((Number) res[2]).intValue());
				data.setCreateDateTime(CommonUtil.getCurrTimes());
				data.setStatType(type13);
				dataList.add(data);
			}
			logger.info("end 9 " + CommonUtil.getCurrTimeForString());
			// // 咨询---自理能力
			sql.delete(0, sql.length());
			sql.append(" select a.customerSelfCareAbility ,a.tenant,count(a.customerSelfCareAbility) from ConsultInfo a  "
					+ " where a.delStatus != ?1 and ( a.advDate>=?2 and a.advDate<=?3 )"
					+ " group by  a.customerSelfCareAbility,a.tenant ");
			List dataConsultSelfCareAbility = this.em
					.createQuery(sql.toString()).setParameter(1, (short) 1)
					.setParameter(2, yesTimeStart).setParameter(3, yesTimeEnd)
					.getResultList();
			it = dataConsultSelfCareAbility.iterator();
			Dicttype type15 = commonService.getDicttype("statType15");
			while (it.hasNext()) {
				Object[] res = (Object[]) it.next();
				StatQuery data = new StatQuery();
				data.setStatResult(((Number) res[2]).intValue());
				data.setStatDate(yesTimeStart);
				data.setStatItemType((Dicttype) res[0]);
				data.setTenant((Tenant) res[1]);
				data.setCreateDateTime(CommonUtil.getCurrTimes());
				data.setDelStatus((short) 0);
				data.setStatType(type15);
				dataList.add(data);
			}
			// 咨询---营销渠道 条件：咨询表 咨询时间 //huibati
			logger.info("stat10:" + CommonUtil.getCurrTimeForString());
			sql.delete(0, sql.length());
			sql.append("select c.advType ,c.info.tenant, count(c.advType) from ConsultAdv c where c.info in"
					+ " ( select a  from ConsultInfo a where a.delStatus != ?1 and a.advDate>=?2 and a.advDate<=?3 ) "
					+ " group by c.advType,c.info.tenant");
			List dataConsultadvWay = this.em.createQuery(sql.toString())
					.setParameter(1, (short) 1).setParameter(2, yesTimeStart)
					.setParameter(3, yesTimeEnd).getResultList();
			it = dataConsultadvWay.iterator();
			Dicttype type12 = commonService.getDicttype("statType12");
			// List type3Items = commonService.getStatItemByType("statType3");
			while (it.hasNext()) {
				Object[] res = (Object[]) it.next();
				StatQuery data = new StatQuery();
				data.setStatDate(yesTimeStart);
				data.setStatItemType((Dicttype) res[0]);
				data.setTenant((Tenant) res[1]);
				data.setStatResult(((Number) res[2]).intValue());
				data.setCreateDateTime(CommonUtil.getCurrTimes());
				data.setStatType(type12);
				data.setDelStatus((short) 0);
				dataList.add(data);
			}
			logger.info("end 10 " + CommonUtil.getCurrTimeForString());
			// 咨询---入住意向 条件：咨询表 咨询时间
			logger.info("stat11：" + CommonUtil.getCurrTimeForString());
			sql.delete(0, sql.length());
			sql.append("select a.liveIntent  ,a.tenant ,count(a.liveIntent)  from ConsultInfo a where a.delStatus != ?1 and ( "
					+ "a.advDate>=?2 and a.advDate<=?3 ) group by a.liveIntent,a.tenant  ");
			List dataConsultLiveIntent = this.em.createQuery(sql.toString())
					.setParameter(1, (short) 1).setParameter(2, yesTimeStart)
					.setParameter(3, yesTimeEnd).getResultList();
			Dicttype type14 = commonService.getDicttype("statType14");
			// List type14Items = commonService.getStatItemByType("statType14");
			it = dataConsultLiveIntent.iterator();
			while (it.hasNext()) {
				Object[] res = (Object[]) it.next();
				StatQuery data = new StatQuery();
				data.setStatDate(yesTimeStart);
				data.setStatItemType((Dicttype) res[0]);
				data.setTenant((Tenant) res[1]);
				data.setStatResult(((Number) res[2]).intValue());
				data.setCreateDateTime(CommonUtil.getCurrTimes());
				data.setStatType(type14);
				data.setDelStatus((short) 0);
				dataList.add(data);
			}
			// 咨询---咨询次数 条件：咨询表 咨询时间
			logger.info("stat12：" + CommonUtil.getCurrTimeForString());
			sql.delete(0, sql.length());
			sql.append("select a.tenant ,count(a)  from ConsultInfo a where a.delStatus != ?1 and ( "
					+ "a.advDate>=?2 and a.advDate<=?3 ) group by a.tenant  ");
			List dataConsultCount = this.em.createQuery(sql.toString())
					.setParameter(1, (short) 1).setParameter(2, yesTimeStart)
					.setParameter(3, yesTimeEnd).getResultList();
			it = dataConsultCount.iterator();
			Dicttype countConsult = commonService.getDicttype("statItem9");
			Dicttype type11 = commonService.getDicttype("statType11");
			while (it.hasNext()) {
				Object[] res = (Object[]) it.next();
				StatQuery data = new StatQuery();
				data.setStatDate(yesTimeStart);
				data.setStatItemType(countConsult);
				data.setTenant((Tenant) res[0]);
				data.setStatResult(((Number) res[1]).intValue());
				data.setCreateDateTime(CommonUtil.getCurrTimes());
				data.setStatType(type11);
				data.setDelStatus((short) 0);
				dataList.add(data);
			}

			// 咨询---销售跟进次数 条件：回访表创建时间
			logger.info("stat13：" + CommonUtil.getCurrTimeForString());
			sql.delete(0, sql.length());
			sql.append("select a.tenant ,count(a)  from BackVistInfo a where a.delStatus != ?1 and ( "
					+ "a.createDateTime>=?2 and a.createDateTime<=?3 ) group by a.tenant  ");
			List dataConsultBack = this.em.createQuery(sql.toString())
					.setParameter(1, (short) 1).setParameter(2, yesTimeStart)
					.setParameter(3, yesTimeEnd).getResultList();
			it = dataConsultBack.iterator();
			Dicttype countConsultBack = commonService.getDicttype("statItem10");
			while (it.hasNext()) {
				Object[] res = (Object[]) it.next();
				StatQuery data = new StatQuery();
				data.setStatDate(yesTimeStart);
				data.setStatItemType(countConsultBack);
				data.setTenant((Tenant) res[0]);
				data.setStatResult(((Number) res[1]).intValue());
				data.setCreateDateTime(CommonUtil.getCurrTimes());
				data.setStatType(type11);
				data.setDelStatus((short) 0);
				dataList.add(data);
			}

			// 咨询---预约参观次数 条件：咨询日期 预约时间不为空
			logger.info("stat14：" + CommonUtil.getCurrTimeForString());
			sql.delete(0, sql.length());
			sql.append("select a.tenant ,count(a)  from ConsultInfo a where a.appointmentDate is not null  and a.delStatus != ?1 and ( "
					+ "a.advDate>=?2 and a.advDate<=?3 ) group by a.tenant  ");
			List dataConsultVist = this.em.createQuery(sql.toString())
					.setParameter(1, (short) 1).setParameter(2, yesTimeStart)
					.setParameter(3, yesTimeEnd).getResultList();
			it = dataConsultVist.iterator();
			Dicttype countConsultVist = commonService.getDicttype("statItem11");
			while (it.hasNext()) {
				Object[] res = (Object[]) it.next();
				StatQuery data = new StatQuery();
				data.setStatDate(yesTimeStart);
				data.setStatItemType(countConsultVist);
				data.setTenant((Tenant) res[0]);
				data.setStatResult(((Number) res[1]).intValue());
				data.setCreateDateTime(CommonUtil.getCurrTimes());
				data.setStatType(type11);
				data.setDelStatus((short) 0);
				dataList.add(data);
			}
			// 批存
			commonService.batchInsert(dataList);
			logger.debug("end");
		} catch (Exception e) {
			logger.debug("create consult stat error :" + e);
		}
	}

	/**
	 *
	 * （每日凌晨2点40执行入住试住退住统计）
	 */
	public void statInTryBackQuery(String yesTime) {
		try {
			//String yesTime = CommonUtil.getYesterdayCurrTimeForString();// 前天日期
			Integer yesTimeStart = CommonUtil.strToIntegerTime(yesTime
					.substring(0, 10) + " 00:00:00");
			Integer yesTimeEnd = CommonUtil.strToIntegerTime(yesTime.substring(
					0, 10) + " 23:59:59");
			List<StatQuery> dataList = new ArrayList<>();
			// yesTimeStart=1494864000;//1494777600;//1494777600;
			// yesTimeEnd=1494950399;//1494863999;//1493740799;
			Iterator it;
			// 入住---试住人数统计 条件： 试住/入住开始时间不为空 试住时间处于查询范围内;
			logger.info("stat1:" + CommonUtil.getCurrTimeForString());
			StringBuilder sql = new StringBuilder(
					" select count(*) as sum ,tenant_id  from tnb_customer_businessflow where  del_status!=1  "
							+ "  and experience_start_time IS NOT NULL AND checkin_start_time IS NOT  NULL "
							+ "  and experience_start_time>= "
							+ yesTimeStart
							+ "  and experience_start_time<= "
							+ yesTimeEnd
							+ "  group by tenant_id ");
			List dataTryCount = getJdbcTemplate().queryForList(sql.toString());
			it = dataTryCount.iterator();
			Dicttype type1 = commonService.getDicttype("statType1");
			Dicttype item2 = commonService.getDicttype("statItem2");
			while (it.hasNext()) {
				Map rece = (Map) it.next();
				StatQuery data = new StatQuery();
				data.setStatDate(yesTimeStart);
				data.setStatItemType(item2);
				Tenant tenant = new Tenant();
				tenant.setId(Long.parseLong(rece.get("tenant_id").toString()));
				data.setTenant(tenant);
				data.setStatResult((Integer
						.parseInt(rece.get("sum").toString())));
				data.setCreateDateTime(CommonUtil.getCurrTimes());
				data.setStatType(type1);
				data.setDelStatus((short) 0);
				dataList.add(data);
			}
			logger.info("end 1 " + CommonUtil.getCurrTimeForString());
			// 入住---入住人数统计 条件：试住时间为空 入住开始时间(最小值)处于查询范围内;
			logger.info("stat2:" + CommonUtil.getCurrTimeForString());
			sql.delete(0, sql.length());
			sql.append("select count(*) as sum ,tenant_id  from tnb_customer_businessflow where  del_status!=1  "
					+ "  and experience_start_time IS  NULL AND checkin_start_time IS NOT NULL "
					+ "  and checkin_start_time>= "
					+ yesTimeStart
					+ "  and checkin_start_time<= "
					+ yesTimeEnd
					+ "  group by tenant_id ");
			List dataCheckInCount = getJdbcTemplate().queryForList(
					sql.toString());
			it = dataCheckInCount.iterator();
			Dicttype item1 = commonService.getDicttype("statItem1");
			while (it.hasNext()) {
				Map rece = (Map) it.next();
				StatQuery data = new StatQuery();
				data.setStatDate(yesTimeStart);
				data.setStatItemType(item1);
				Tenant tenant = new Tenant();
				tenant.setId(Long.parseLong(rece.get("tenant_id").toString()));
				data.setTenant(tenant);
				data.setStatResult((Integer
						.parseInt(rece.get("sum").toString())));
				data.setCreateDateTime(CommonUtil.getCurrTimes());
				data.setStatType(type1);
				data.setDelStatus((short) 0);
				dataList.add(data);
			}
			logger.info("end 2 " + CommonUtil.getCurrTimeForString());
			// 入住---试住入住总人数统计 条件：试住/入住开始时间(最小值)处于查询范围内;
			logger.info("stat3:" + CommonUtil.getCurrTimeForString());
			sql.delete(0, sql.length());
			sql.append("select count(*) as sum ,tenant_id  from tnb_customer_businessflow where  del_status!=1  "
					+ "  and checkin_start_time IS NOT NULL "
					+ "  and checkin_start_time>= "
					+ yesTimeStart
					+ "  and checkin_start_time<= "
					+ yesTimeEnd
					+ "  group by tenant_id ");
			List dataInSumCount = getJdbcTemplate()
					.queryForList(sql.toString());
			it = dataInSumCount.iterator();
			Dicttype item3 = commonService.getDicttype("statItem3");
			while (it.hasNext()) {
				Map rece = (Map) it.next();
				StatQuery data = new StatQuery();
				data.setStatDate(yesTimeStart);
				data.setStatItemType(item3);
				Tenant tenant = new Tenant();
				tenant.setId(Long.parseLong(rece.get("tenant_id").toString()));
				data.setTenant(tenant);
				data.setStatResult((Integer
						.parseInt(rece.get("sum").toString())));
				data.setCreateDateTime(CommonUtil.getCurrTimes());
				data.setStatType(type1);
				data.setDelStatus((short) 0);
				dataList.add(data);
			}
			logger.info("end 3 " + CommonUtil.getCurrTimeForString());
			// 入住---学历统计 条件：试住/入住开始时间(最小值)处于查询范围内;
			logger.info("stat4:" + CommonUtil.getCurrTimeForString());
			sql.delete(0, sql.length());
			sql.append("select educationLevel_id as item ,count(*) as sum ,tenant_id  from tnb_customer t where educationLevel_id is not null and  t.id in "
					+ "  (select customer_id from tnb_customer_businessflow where customer_id is not null and del_status!=1  "
					+ "  and checkin_start_time IS NOT NULL "
					+ "  and checkin_start_time>= "
					+ yesTimeStart
					+ "  and checkin_start_time<= "
					+ yesTimeEnd
					+ " ) group by educationLevel_id,tenant_id ");
			List dataConsultAgeCount = getJdbcTemplate().queryForList(
					sql.toString());
			it = dataConsultAgeCount.iterator();
			Dicttype type7 = commonService.getDicttype("statType7");
			while (it.hasNext()) {
				Map rece = (Map) it.next();
				StatQuery data = new StatQuery();
				data.setStatDate(yesTimeStart);
				data.setStatItemType(commonService.getDicttypeById((Long
						.parseLong(rece.get("item").toString()))));
				Tenant tenant = new Tenant();
				tenant.setId(Long.parseLong(rece.get("tenant_id").toString()));
				data.setTenant(tenant);
				data.setStatResult((Integer
						.parseInt(rece.get("sum").toString())));
				data.setCreateDateTime(CommonUtil.getCurrTimes());
				data.setStatType(type7);
				data.setDelStatus((short) 0);
				dataList.add(data);
			}
			logger.info("end 4 " + CommonUtil.getCurrTimeForString());
			// 入住---性别统计 条件：试住/入住开始时间(最小值)处于查询范围内;
			logger.info("stat5:" + CommonUtil.getCurrTimeForString());
			sql.delete(0, sql.length());
			sql.append("select sex_id as item ,count(*) as sum ,tenant_id  from tnb_customer t where sex_id is not null and t.id in "
					+ " (select customer_id from tnb_customer_businessflow where customer_id is not null and del_status!=1  "
					+ "  and checkin_start_time IS NOT NULL "
					+ "  and checkin_start_time>= "
					+ yesTimeStart
					+ "  and checkin_start_time<= "
					+ yesTimeEnd
					+ " ) group by sex_id,tenant_id ");
			List dataInSexCount = getJdbcTemplate()
					.queryForList(sql.toString());
			it = dataInSexCount.iterator();
			Dicttype type5 = commonService.getDicttype("statType5");
			while (it.hasNext()) {
				Map rece = (Map) it.next();
				StatQuery data = new StatQuery();
				data.setStatDate(yesTimeStart);
				data.setStatItemType(commonService.getDicttypeById((Long
						.parseLong(rece.get("item").toString()))));
				Tenant tenant = new Tenant();
				tenant.setId(Long.parseLong(rece.get("tenant_id").toString()));
				data.setTenant(tenant);
				data.setStatResult((Integer
						.parseInt(rece.get("sum").toString())));
				data.setCreateDateTime(CommonUtil.getCurrTimes());
				data.setStatType(type5);
				data.setDelStatus((short) 0);
				dataList.add(data);
			}
			logger.info("end 5 " + CommonUtil.getCurrTimeForString());
			// 入住---自理能力统计 条件：试住/入住开始时间(最小值)处于查询范围内;
			logger.info("stat6:" + CommonUtil.getCurrTimeForString());
			sql.delete(0, sql.length());
			sql.append("select selfCareAbility_id as item,count(*) as sum ,tenant_id  from tnb_customer t where selfCareAbility_id is not null and t.id in "
					+ " (select customer_id from tnb_customer_businessflow where customer_id is not null and del_status!=1  "
					+ "  and checkin_start_time IS NOT NULL "
					+ "  and checkin_start_time>= "
					+ yesTimeStart
					+ "  and checkin_start_time<= "
					+ yesTimeEnd
					+ " ) group by selfCareAbility_id,tenant_id ");
			List dataInSelfCareCount = getJdbcTemplate().queryForList(
					sql.toString());
			it = dataInSelfCareCount.iterator();
			Dicttype type4 = commonService.getDicttype("statType4");
			while (it.hasNext()) {
				Map rece = (Map) it.next();
				StatQuery data = new StatQuery();
				data.setStatDate(yesTimeStart);
				data.setStatItemType(commonService.getDicttypeById((Long
						.parseLong(rece.get("item").toString()))));
				Tenant tenant = new Tenant();
				tenant.setId(Long.parseLong(rece.get("tenant_id").toString()));
				data.setTenant(tenant);
				data.setStatResult((Integer
						.parseInt(rece.get("sum").toString())));
				data.setCreateDateTime(CommonUtil.getCurrTimes());
				data.setStatType(type4);
				data.setDelStatus((short) 0);
				dataList.add(data);
			}
			logger.info("end 6 " + CommonUtil.getCurrTimeForString());
			// 入住---年龄统计 条件：试住/入住开始时间(最小值)处于查询范围内;
			logger.info("stat7:" + CommonUtil.getCurrTimeForString());
			sql.delete(0, sql.length());
			sql.append("select elt(interval(t.age,0,70,80,90,100),'0-70','70-79','80-89', '90-99','> 100') as `ages`,count(*) as `sum` ,tenant_id "
					+ "from tnb_customer t where t.age is not null and t.id in "
					+ " (select customer_id from tnb_customer_businessflow where customer_id is not null and del_status!=1  "
					+ "  and checkin_start_time IS NOT NULL "
					+ "  and checkin_start_time>= "
					+ yesTimeStart
					+ "  and checkin_start_time<= "
					+ yesTimeEnd
					+ " ) group by elt(interval(t.age,0,70,80,90,100),'0-70','70-79','80-89', '90-99','> 100'),tenant_id ");
			List dataInAgeCount = getJdbcTemplate()
					.queryForList(sql.toString());
			it = dataInAgeCount.iterator();
			Dicttype type6 = commonService.getDicttype("statType6");
			Map<String, Dicttype> ageDicttype = new HashMap<>();
			ageDicttype.put("0-70", commonService.getDicttype("statItem4"));
			ageDicttype.put("70-79", commonService.getDicttype("statItem5"));
			ageDicttype.put("80-89", commonService.getDicttype("statItem6"));
			ageDicttype.put("90-99", commonService.getDicttype("statItem7"));
			ageDicttype.put("≥100", commonService.getDicttype("statItem8"));
			while (it.hasNext()) {
				Map rece = (Map) it.next();
				StatQuery data = new StatQuery();
				data.setStatDate(yesTimeStart);
				if ("0-70".equals(rece.get("ages").toString())) {
					data.setStatItemType(ageDicttype.get("0-70"));
				}
				if ("70-79".equals(rece.get("ages").toString())) {
					data.setStatItemType(ageDicttype.get("70-79"));
				}
				if ("80-89".equals(rece.get("ages").toString())) {
					data.setStatItemType(ageDicttype.get("80-89"));
				}
				if ("90-99".equals(rece.get("ages").toString())) {
					data.setStatItemType(ageDicttype.get("90-99"));
				}
				if ("> 100".equals(rece.get("ages").toString())) {
					data.setStatItemType(ageDicttype.get("≥100"));
				}
				Tenant tenant = new Tenant();
				tenant.setId(Long.parseLong(rece.get("tenant_id").toString()));
				data.setTenant(tenant);
				data.setStatResult(Integer.parseInt(rece.get("sum").toString()));
				data.setCreateDateTime(CommonUtil.getCurrTimes());
				data.setStatType(type6);
				data.setDelStatus((short) 0);
				dataList.add(data);
			}
			logger.info("end 7 " + CommonUtil.getCurrTimeForString());
			// 入住分析---营销渠道
			sql.delete(0, sql.length());
			sql.append("select t.advway_id ,count(*) as result ,t2.tenant_id from tnb_businessflow_adv t  "
					+ " INNER JOIN tnb_customer_businessflow t2 "
					+ " INNER JOIN tnb_tenant t3 ON t2.tenant_id = t3.id "
					+ " WHERE  t2.id=t.flow_id AND t.flow_id in ( select id from tnb_customer_businessflow where  del_status!=1  "
					+ "  and checkin_start_time IS NOT NULL "
					+ "  and checkin_start_time>= "
					+ yesTimeStart
					+ "  and checkin_start_time<= "
					+ yesTimeEnd
					+ ")  group by advway_id,tenant_id ");
			List dataAdvCount = getJdbcTemplate().queryForList(sql.toString());
			it = dataAdvCount.iterator();
			Dicttype type3 = commonService.getDicttype("statType3");
			while (it.hasNext()) {
				Map rece = (Map) it.next();
				StatQuery data = new StatQuery();
				data.setStatDate(yesTimeStart);
				data.setStatItemType(commonService.getDicttypeById((Long
						.parseLong(rece.get("advway_id").toString()))));
				Tenant tenant = new Tenant();
				tenant.setId(Long.parseLong(rece.get("tenant_id").toString()));
				data.setTenant(tenant);
				data.setStatResult(Integer.parseInt(rece.get("result")
						.toString()));
				data.setCreateDateTime(CommonUtil.getCurrTimes());
				data.setStatType(type3);
				data.setDelStatus((short) 0);
				dataList.add(data);
			}
			// 入住分析---入住偏好分析 条件：试住/入住开始时间(最小值)处于查询范围内; //huibati
			logger.info("stat8:" + CommonUtil.getCurrTimeForString());
			sql.delete(0, sql.length());
			sql.append("select t.inpreference_id ,count(*) as result ,t2.tenant_id from tnb_customer_businessflow_inpreference t  "
					+ " INNER JOIN tnb_customer_businessflow t2 "
					+ " INNER JOIN tnb_tenant t3 ON t2.tenant_id = t3.id "
					+ " WHERE  t2.id=t.businessflow_id AND t.businessflow_id in ( select id from tnb_customer_businessflow where  del_status!=1  "
					+ "  and checkin_start_time IS NOT NULL "
					+ "  and checkin_start_time>= "
					+ yesTimeStart
					+ "  and checkin_start_time<= "
					+ yesTimeEnd
					+ " ) group by inpreference_id,tenant_id ");
			List dataInPreferenceCount = getJdbcTemplate().queryForList(
					sql.toString());
			it = dataInPreferenceCount.iterator();
			Dicttype type2 = commonService.getDicttype("statType2");
			while (it.hasNext()) {
				Map rece = (Map) it.next();
				StatQuery data = new StatQuery();
				data.setStatDate(yesTimeStart);
				data.setStatItemType(commonService.getDicttypeById((Long
						.parseLong(rece.get("inpreference_id").toString()))));
				Tenant tenant = new Tenant();
				tenant.setId(Long.parseLong(rece.get("tenant_id").toString()));
				data.setTenant(tenant);
				data.setStatResult(Integer.parseInt(rece.get("result")
						.toString()));
				data.setCreateDateTime(CommonUtil.getCurrTimes());
				data.setStatType(type2);
				data.setDelStatus((short) 0);
				dataList.add(data);
			}
			logger.info("end 8 " + CommonUtil.getCurrTimeForString());
			// 退住---退住人数 条件：退住日期
			logger.info("stat15：" + CommonUtil.getCurrTimeForString());
			sql.delete(0, sql.length());
			sql.append("select a.tenant ,count(a)  from CustomerBusinessFlow a where a.delStatus != ?1 and a.leaveDate is not null and ( "
					+ "a.leaveDate>=?2 and a.leaveDate<=?3 ) group by a.tenant  ");
			List leaveCount = this.em.createQuery(sql.toString())
					.setParameter(1, (short) 1).setParameter(2, yesTimeStart)
					.setParameter(3, yesTimeEnd).getResultList();
			it = leaveCount.iterator();
			Dicttype type9 = commonService.getDicttype("statType9");
			Dicttype leaveItem = commonService.getDicttype("statItem12");
			while (it.hasNext()) {
				Object[] res = (Object[]) it.next();
				StatQuery data = new StatQuery();
				data.setDelStatus((short) 0);
				data.setStatDate(yesTimeStart);
				data.setStatItemType(leaveItem);
				data.setTenant((Tenant) res[0]);
				data.setStatResult(((Number) res[1]).intValue());
				data.setCreateDateTime(CommonUtil.getCurrTimes());
				data.setStatType(type9);
				data.setDelStatus((short) 0);
				dataList.add(data);
			}

			// 退住---退住原因 条件：退住日期
			logger.info("stat16：" + CommonUtil.getCurrTimeForString());
			sql.delete(0, sql.length());
			sql.append("select a.leaveReason  ,a.tenant ,count(a.leaveReason)  from CustomerBusinessFlow a where a.delStatus != ?1 and a.leaveDate is not null and ( "
					+ "a.leaveDate>=?2 and a.leaveDate<=?3 ) group by a.leaveReason,a.tenant  ");
			List leaveReasonCount = this.em.createQuery(sql.toString())
					.setParameter(1, (short) 1).setParameter(2, yesTimeStart)
					.setParameter(3, yesTimeEnd).getResultList();
			it = leaveReasonCount.iterator();
			Dicttype type10 = commonService.getDicttype("statType10");
			while (it.hasNext()) {
				Object[] res = (Object[]) it.next();
				StatQuery data = new StatQuery();
				data.setDelStatus((short) 0);
				data.setStatDate(yesTimeStart);
				data.setStatItemType((Dicttype) res[0]);
				data.setTenant((Tenant) res[1]);
				data.setStatResult(((Number) res[2]).intValue());
				data.setCreateDateTime(CommonUtil.getCurrTimes());
				data.setStatType(type10);
				data.setDelStatus((short) 0);
				dataList.add(data);
			}
			// 批存
			commonService.batchInsert(dataList);
			logger.debug("end");
		} catch (Exception e) {
			logger.debug("create stat error :" + e);
		}

	}
	
	
	public void attendance() {
		Integer yesTimeS = 0;
		Integer yesTimeE = 0;
		try {
			yesTimeS = CommonUtil
					.strToIntegerTime(CommonUtil.getYesterdayCurrTimeForString().substring(0, 10) + " 00:00:00");
			yesTimeE = CommonUtil
					.strToIntegerTime(CommonUtil.getYesterdayCurrTimeForString().substring(0, 10) + " 23:59:59");
		} catch (ParseException e) {
			logger.error("pase date error:" + e);
		}
		List statData = new ArrayList<>();
		String queryWorkTime = " select c from PersonLocation c where c.employee is not null and c.delStatus!=1 and c.locationTime>=?1 and c.locationTime<=?2 order by c.employee.id,c.locationTime";
		List<PersonLocation> ssList = this.em.createQuery(queryWorkTime).setParameter(1, yesTimeS)
				.setParameter(2, yesTimeE).getResultList();
		employeeService.createWorkTimeByLocation(ssList);
		
		
	}
	
	
	
	public void testTanPing(){
		// 建立 Message提醒
		Short detailType = (short) ICommonConstants.REMINDDETAILTYPE.HBEACONCALL.ordinal(); // 提醒详细类型
		Message message = new Message();
		message.setType(
				new Integer(CommonConstants.MESSAGE.MESSAGETYPE_NOTICE.label()).shortValue());
		message.setCode(CommonConstants.MESSAGE.MESSAGECODE_CALLINGNOTICE.label());
		message.setCreateTime(CommonUtil.getCurrTimes());
		message.setType((short) ICommonConstants.MESSAGETYPE.REMIND.ordinal());
		message.setDetailType(detailType);
		Tenant tenant=new Tenant();
        tenant.setId(1002L);
		message.setTenant(tenant);
		// 设置content debugTest
		Map<String, String> msgMap = new HashMap<String, String>();
		// message.setToUser(user);
		// 设置消息内容
		msgMap.put("createTime", CommonUtil.getCurrTimes()+"");

		msgMap.put("customerId", "129");
		msgMap.put("customerName", "于怀芳test");

		msgMap.put("bedId", "141");
		// msgMap.put("bedName", bed.getName());
		msgMap.put("content", CommonConstants.BUTTON_CALLING_TYPE.NORMAL_NOTE.label());
		message.setContent(CommonUtil.mapToJsonString(msgMap));
		// 使用类型作为通知的业务类型
		message.setBusiness("buttonCalling");
		message.setHasRead(new Integer(0).shortValue());
		// 取得需要发送的用户--发送给所有本机构的人员
		User user=new User();
		user.setId(19L);
		List<User> users=new ArrayList<>();
		users.add(user);
		message.setUserList(users);
//		dslJpaService.setEntityClass(Message.class);
//		message = (Message) dslJpaService.create(message);
		logger.debug("testTanping ");
		ActionContextHolder.getMessageQueue().addWarningMsg(message);
	}
	
	
	
	
	/**
	 * 生成按键呼叫消息
	 */
	@Transactional
	public void buildHbeaconCallingMessage(String jsonData) {
		logger.debug("create hbeacon calling 1 "+jsonData);
		JSONObject data = JSONObject.fromObject(jsonData);
		JSONArray deviceData = data.getJSONArray("callingData");
		String dataSource = data.containsKey("dataSource") ? data.getString("dataSource") : null;
		List<Long> deviceIds=new ArrayList<>();
		for (int i = 0; i < deviceData.size(); i++) {
			String deviceId = deviceData.getJSONObject(i).getString("deviceId");
			String scannerId = "";
			if (deviceData.getJSONObject(i).containsKey("scannerId")) {
				scannerId = deviceData.getJSONObject(i).getString("scannerId");
			}
			Device device=deviceService.getDeviceWithDeviceNo(deviceId);
			if(device!=null){
				deviceIds.add(device.getId());
			}
			Device scanner=deviceService.getDeviceWithDeviceNo(scannerId);
			if(scanner!=null){
				deviceIds.add(scanner.getId());
			}
			Integer callingTime = -1;
			String eventType="-1";
			if (deviceData.getJSONObject(i).containsKey("callingTime")) {
				callingTime = deviceData.getJSONObject(i).getInt("callingTime");
				if (deviceData.getJSONObject(i).containsKey("eventType")) {
					eventType = deviceData.getJSONObject(i).getString("eventType");
				}
			}
			Integer cancelTime = -1;
			if (deviceData.getJSONObject(i).containsKey("cancelTime")) {
				cancelTime = deviceData.getJSONObject(i).getInt("cancelTime");
			}
			Bed bed = workPlaceService.getBedWithDeviceNo(deviceId.toString());
			if (bed != null) {
				Customer customer = customerService.getCustomerWithBed(bed.getId());
				if (callingTime != -1) { // 按键呼叫信息
					//huami按键进行未复位判断
					if (dataSource == null || "huami".equals(dataSource)) {
						// 按键未复位此次消息不进行处理
						if (ActionContextHolder.getCallingList() != null
								&& ActionContextHolder.getCallingList().contains(deviceId)) {
							return;
						}
						// 将huami按键加入未复位列表
						if (ActionContextHolder.getCallingList() != null) {
							ActionContextHolder.getCallingList().add(deviceId);
						} else {
							ActionContextHolder.setCallingList(new ArrayList<String>());
							ActionContextHolder.getCallingList().add(deviceId);
						}
					}
					List<User> users = userService.getAllUserList(bed.getTenant());// 本机构所有人
					if (users != null && users.size() > 0) {
							// 建立 Message提醒
							Short detailType = (short) ICommonConstants.REMINDDETAILTYPE.HBEACONCALL.ordinal(); // 提醒详细类型
							Message message = new Message();
							message.setType(
									new Integer(CommonConstants.MESSAGE.MESSAGETYPE_NOTICE.label()).shortValue());
							message.setCode(CommonConstants.MESSAGE.MESSAGECODE_CALLINGNOTICE.label());
							message.setCreateTime(CommonUtil.getCurrTimes());
							message.setType((short) ICommonConstants.MESSAGETYPE.REMIND.ordinal());
							message.setDetailType(detailType);
							message.setTenant(bed.getTenant());
							// 设置content debugTest
							Map<String, String> msgMap = new HashMap<String, String>();
							// message.setToUser(user);
							// 设置消息内容
							msgMap.put("createTime", callingTime.toString());
							if (customer != null) {
								msgMap.put("customerId", customer.getId().toString());
								msgMap.put("customerName", customer.getName());
							}
							msgMap.put("bedId", bed.getId().toString());
							// msgMap.put("bedName", bed.getName());
							msgMap.put("content", CommonConstants.BUTTON_CALLING_TYPE.NORMAL_NOTE.label());
							message.setContent(CommonUtil.mapToJsonString(msgMap));
							// 使用类型作为通知的业务类型
							message.setBusiness("buttonCalling");
							message.setHasRead(new Integer(0).shortValue());
							// 取得需要发送的用户--发送给所有本机构的人员
							message.setUserList(users);
							ActionContextHolder.getMessageQueue().addWarningMsg(message);
							if(dataSource!=null&&"test".equals(dataSource)){
								return;
							}

	
					}

				} // callingTime
				if (cancelTime != -1) {// 按键呼叫取消信息
					//将该huami设备id从未复位按键列表中清除
					if (!"".equals(deviceId) && deviceId != null
							&& ActionContextHolder.getCallingList().contains(deviceId)) {
						ActionContextHolder.getCallingList().remove(deviceId);
					}
					StringBuilder sqlHb =new StringBuilder("select c from HbeaconWarning c where c.delStatus != ?1 and c.createDateTime>1506331394  and c.scannerId=?2 and ( c.flag.typecode=?3 or ( c.flag.typecode=?4 and c.cancelTime is null ) )");
					List dataList = this.em
							.createQuery(sqlHb.toString())
							.setParameter(1, (short) 1).setParameter(2, deviceId)
							.setParameter(3, CommonConstants.DICTTYPE.BUTTON_STATUS_1.label())
							.setParameter(4, CommonConstants.DICTTYPE.BUTTON_STATUS_3.label()).getResultList();
					if (dataList != null && dataList.size() > 0) {
						HbeaconWarning warning = (HbeaconWarning) dataList.get(0);
						warning.setCancelTime(cancelTime);
						warning.setLastUpdateDateTime(CommonUtil.getCurrTimes());
						if(warning.getFlag().getTypecode().equals(CommonConstants.DICTTYPE.BUTTON_STATUS_1.label())){
							warning.setFlag(commonService.getDicttype(CommonConstants.DICTTYPE.BUTTON_STATUS_2.label())); 
						}

						List<User> users = userService.getAllUserList(bed.getTenant());// 本机构所有人
						if (users != null && users.size() > 0) {
							// 建立 Message提醒
							Short detailType = (short) ICommonConstants.REMINDDETAILTYPE.HBEACONCALL.ordinal(); // 提醒详细类型
							Message message = new Message();
							message.setType(
									new Integer(CommonConstants.MESSAGE.MESSAGETYPE_NOTICE.label()).shortValue());
							message.setCode(CommonConstants.MESSAGE.MESSAGECODE_CALLINGNOTICE.label());
							message.setCreateTime(CommonUtil.getCurrTimes());
							message.setType((short) ICommonConstants.MESSAGETYPE.REMIND.ordinal());
							message.setDetailType(detailType);
							message.setTenant(bed.getTenant());
							// 设置content debugTest
							Map<String, String> msgMap = new HashMap<String, String>();
							// message.setToUser(user);
							// 设置消息内容
							msgMap.put("createTime", callingTime.toString());
							if (customer != null) {
								msgMap.put("customerId", customer.getId().toString());
								msgMap.put("customerName", customer.getName());
							}
							msgMap.put("bedId", bed.getId().toString());
							msgMap.put("content", "取消按键呼叫");
							message.setContent(CommonUtil.mapToJsonString(msgMap));
							// 使用类型作为通知的业务类型
							message.setBusiness("cancelButtonCalling");
							message.setHasRead(new Integer(0).shortValue());
							// 取得需要发送的用户--发送给所有本机构的人员
							message.setUserList(users);
							ActionContextHolder.getMessageQueue().addWarningMsg(message);
						}
					}
				}
			} // bed!=null
		} // for
	}
	
	

	@Transactional
	public void operateBackAndServerChange() {
		String dayTime = "";// 昨天日期
		Integer yesTimeStart = null;
		Integer yesTimeEnd = null;
		String curTime = "";// 当日日期
		Integer curTimeStart = null;
		Integer curTimeEnd = null;
		try {
			dayTime = CommonUtil.getYesterdayCurrTimeForString();// 前天日期
			yesTimeStart = CommonUtil.strToIntegerTime(dayTime.substring(0, 10)
					+ " 00:00:00");
			yesTimeEnd = CommonUtil.strToIntegerTime(dayTime.substring(0, 10)
					+ " 23:59:59");
			curTime = CommonUtil.getCurrTimeForString();// 当日日期
			curTimeStart = CommonUtil.strToIntegerTime(curTime.substring(0, 10)
					+ " 00:00:00");
			curTimeEnd = CommonUtil.strToIntegerTime(curTime.substring(0, 10)
					+ " 23:59:59");
		} catch (Exception e) {
			logger.debug("create bill error :" + e);
		}
		List<NursingProject> projects = new ArrayList<>();
		projects = this.em.createQuery("select c from NursingProject c where c.delStatus!=1 ").getResultList();
		Map<Customer, NursingLevel> projectMap = (Map<Customer, NursingLevel>) projects.stream().collect(
				Collectors.toMap(NursingProject::getCustomer, NursingProject::getLevel, (key1, key2) -> key2));
		// 对费用结算中的退住 进行操作 退住申请---操作为费用结算中
		logger.info("start operateCustomerBack "
				+ CommonUtil.getCurrTimeForString());
		// 处理昨天到期的退住
		List<CustomerBusinessFlow> flowYList = this.em
				.createQuery(
						"select a from CustomerBusinessFlow a where a.delStatus != ?1 and a.leaveStatus.typecode=?2 and a.leaveDate>=?3 and a.leaveDate<=?4 ")
				.setParameter(1, (short) 1)
				.setParameter(2,
						CommonConstants.DICTTYPE.FLOW_LEAVE_STATUS_4.label())
				.setParameter(3, yesTimeStart).setParameter(4, yesTimeEnd)
				.getResultList();
		for (CustomerBusinessFlow flow : flowYList) {
			customerService.operateCustomerBack(flow, flow.getCustomer(), 1,projectMap);
		}
		// 处理当天到期的退住
		List<CustomerBusinessFlow> flowList = this.em
				.createQuery(
						"select a from CustomerBusinessFlow a where a.delStatus != ?1 and a.leaveStatus.typecode=?2 and a.leaveDate>=?3 and a.leaveDate<=?4 ")
				.setParameter(1, (short) 1)
				.setParameter(2,
						CommonConstants.DICTTYPE.FLOW_LEAVE_STATUS_4.label())
				.setParameter(3, curTimeStart).setParameter(4, curTimeEnd)
				.getResultList();
		for (CustomerBusinessFlow flow : flowList) {
			customerService.operateCustomerBack(flow, flow.getCustomer(), 0,projectMap);
		}
		// 服务变更列表 服务变更生效时间 到了 才处理 或者是生效时间小于今天且未处理
		logger.info("start createDayBill(back)&&operateCustomerServerChange "
				+ CommonUtil.getCurrTimeForString());
		List<CustomerServerChange> changeList = this.em
				.createQuery(
						"select a from CustomerServerChange a where a.delStatus != ?1 and a.status.typecode=?2 and a.effectiveTime<=?3  ")
				.setParameter(1, (short) 1).setParameter(2, "applyStatus1")
				.setParameter(3, CommonUtil.getCurrTimes()).getResultList();
		List<Long> cuList = new ArrayList<>();
		for (CustomerServerChange record : changeList) {
			String effectiveTime = CommonUtil.integetTimeForString(
					record.getEffectiveTime()).substring(0, 10);
			if (record.getCustomer().getId() != null) {
				cuList.add(record.getCustomer().getId());
				// 生效日期是昨天生效的 先更改老人服务相关信息 再生成昨天账单
				if (dayTime.substring(0, 10).equals(effectiveTime)) {
						customerService.operateCustomerServerChange(record);
						Integer timeAddBillStart=record.getEffectiveTime();
						accountService.createDayBill(record.getCustomer(), dayTime,projectMap);
					} 
				}	
				// 生效日期是今天生效的 先生成昨天账单 再生效
				if (curTime.substring(0, 10).equals(effectiveTime)) { 
					accountService.createDayBill(record.getCustomer(), dayTime,projectMap);
					customerService.operateCustomerServerChange(record);
				}
				// 生效日期在今日以前的 先更改老人服务相关信息 补充生成日账单
				if (!curTime.substring(0, 10).equals(effectiveTime)) { 
					customerService.operateCustomerServerChange(record);
					Integer timeAddBillStart=record.getEffectiveTime();
					while(timeAddBillStart<yesTimeStart){
						String timeAddBillStartString =CommonUtil.integetTimeForString(timeAddBillStart);
						accountService.createDayBill(record.getCustomer(), timeAddBillStartString,projectMap);
						timeAddBillStart += 24 * 60 * 60;
					}

			}
		}

	}

	
}
